package server.datasource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import server.dao.util.DatabaseConstants;
import server.idatasource.IUserRightDataSource;

public class UserRightDataSource implements IUserRightDataSource {
	private Connection	connection;

	private List<List<String>> getResultList(ResultSet resultSet) throws SQLException {

		List<List<String>> resultList = new ArrayList<List<String>>();
		while (resultSet.next()) {
			List<String> result = new ArrayList<String>();
			int i = 1;
			while (i <= 2) {
				result.add(resultSet.getString(i));
				i++;
			}
			resultList.add(result);
		}
		return resultList;
	}

	private Connection getConnection() {

		try {
			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			connection = DriverManager.getConnection(DatabaseConstants.URL, DatabaseConstants.USERNAME, DatabaseConstants.PASSWORD);
		} catch (Exception e) {
			return null;
		}
		return connection;
	}

	public List<String> handleError() {
		List<String> result = new ArrayList<String>();
		result.add("ERROR");
		return result;
	}

	public List<List<String>> getAllRightsByUser(String username) {
		List<List<String>> resultList = new ArrayList<List<String>>();
		Statement statement = null;
		String query = "";
		ResultSet resultSet = null;

		try {
			//Create connection object
			connection = getConnection();
			//Create Statement object
			statement = connection.createStatement();
			query = "SELECT * FROM " + DatabaseConstants.RIGHT_TABLE + " WHERE " + DatabaseConstants.RIGHT_TABLE_RIGHTID + " in " + "(SELECT " + DatabaseConstants.USER_RIGHT_TABLE_USERID + " FROM "
					+ DatabaseConstants.USER_RIGHT_TABLE + " WHERE " + DatabaseConstants.USER_RIGHT_TABLE_USERID + " = " + username + ")";
			//Execute the query
			resultSet = statement.executeQuery(query);
			resultList = getResultList(resultSet);
		} catch (Exception e) {
			resultList.add(handleError());
		} finally {
			try {
				connection.close();
				resultSet.close();
				statement.close();
			} catch (Exception e) {
				resultList.add(handleError());
			}
		}
		return resultList;
	}

	/*Inserts a new right to a specified user given in the argument, into the database. Returns a string, with the
	 * value "OK" if the operation was successful, else returns "ERROR".*/
	@Override
	public String insertUserRight(String username, int rightID) {
		Statement statement;
		String command = "";
		String error = "OK";

		try {
			//Create connection object
			connection = getConnection();
			//Create Statement object
			statement = connection.createStatement();
			command = "INSERT INTO " + DatabaseConstants.USER_RIGHT_TABLE + " VALUES ('" + username + "' ," + rightID + ")";
			//Execute the command
			statement.executeUpdate(command);
		} catch (Exception e) {
		} finally {
			try {
				connection.close();
			} catch (Exception e) {
				error = e.toString();
			}
		}
		return error;
	}

	@Override
	/*Deletes a userRight from database, having the username and rightID given as argument. Returns a string, with the
	 * value "OK" if the operation was successful, else returns "ERROR".*/
	public String deleteUserRight(String username, int rightId) {
		Statement statement;
		String command = "";
		String error = "OK";

		try {
			//Create connection object
			connection = getConnection();
			//Create Statement object
			statement = connection.createStatement();
			command = "DELETE FROM " + DatabaseConstants.USER_RIGHT_TABLE + " WHERE " + DatabaseConstants.USER_RIGHT_TABLE_USERID + " = '" + username + "' and " + DatabaseConstants.USER_RIGHT_TABLE_RIGHTID
					+ " = " + rightId;
			//Execute the command
			statement.executeUpdate(command);
		} catch (Exception e) {
		} finally {
			try {
				connection.close();
			} catch (Exception e) {
				error = e.toString();
			}
		}
		return error;
	}

}
